/*
USE master
GO
DROP DATABASE HighSchoolManageSystem
GO
*/
CREATE DATABASE HighSchoolManageSystem
GO
USE HighSchoolManageSystem
GO

CREATE TABLE [user]
(
  [uid] int IDentity(1,1) NOT NULL,
  [user] nvarchar(50) NOT NULL,
  pass nvarchar(25) NOT NULL,
  [level] int NOT NULL,
  CONSTRAINT PK_user PRIMARY KEY([uid])
)
GO
CREATE TABLE [subject] 
(
  sjid int IDentity(1,1) NOT NULL,
  mmh int NOT NULL,
  name nvarchar(150) NOT NULL,
  CONSTRAINT PK_subject PRIMARY KEY(sjid)
) 
GO
CREATE TABLE assigned_subjects
(
  asid int NOT NULL ,
  [uid] int NOT NULL,
  sjid int NOT NULL,
  CONSTRAINT PK_assigned_subjects PRIMARY KEY(asid),
  CONSTRAINT FK_assigned_subjects_user FOREIGN KEY ([uid]) REFERENCES [user]([uid]),
  CONSTRAINT FK_assigned_subjects_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
) 
GO
CREATE TABLE  class 
(
  clid int IDentity(1,1) NOT NULL,
  [uid] int NOT NULL,
  idclass nvarchar(15) NOT NULL,
  nop int ,
  bio nvarchar(100),
 CONSTRAINT PK_class PRIMARY KEY(clid),
 CONSTRAINT FK_class_user FOREIGN KEY ([uid]) REFERENCES [user]([uid])
) 
GO
CREATE TABLE parents
(
  pid int IDentity(1,1) NOT NULL,
  [uid] int NOT NULL,
  name nvarchar(150) NOT NULL,
  birthday date ,
  sex int,
  avatar nvarchar(150) ,
  bio text,
  email nvarchar(150),
  [address] nvarchar(250) NOT NULL,
  CONSTRAINT PK_parents PRIMARY KEY(pid),
  CONSTRAINT FK_parents_user FOREIGN KEY ([uid]) REFERENCES [user]([uid])
)
GO
CREATE TABLE students
(
  [sid] int IDentity(1,1) NOT NULL,
  pid int NOT NULL,
  idstudents nvarchar(100) NOT NULL,
  name nvarchar(150) NOT NULL,
  sex int,
  birthday date,
  [address] nvarchar(250),
  bio text,
  avatar nvarchar(150),
  CONSTRAINT PK_students PRIMARY KEY([sid]),
  CONSTRAINT FK_students_parents FOREIGN KEY ([pid]) REFERENCES parents(pid)
) 
GO
CREATE TABLE classlist
(
  cllid int IDentity(1,1) NOT NULL,
  clid int NOT NULL,
  [sid] int NOT NULL,
  CONSTRAINT PK_classlist PRIMARY KEY(cllid),
  CONSTRAINT FK_classlist_class FOREIGN KEY (clid) REFERENCES class(clid),
  CONSTRAINT FK_classlist_students FOREIGN KEY ([sid]) REFERENCES students([sid])
) 
GO
CREATE TABLE sms
(
  smsid int IDentity(1,1) NOT NULL,
  [uid] int NOT NULL,
  contents text NOT NULL,
  [date] date NOT NULL,
  CONSTRAINT PK_sms PRIMARY KEY(smsid),
  CONSTRAINT FK_sms_user FOREIGN KEY ([uid]) REFERENCES [user]([uid])
)
GO
CREATE TABLE list_send_sms
(
  lssid int IDentity(1,1) NOT NULL,
  smsid int NOT NULL,
  pid int NOT NULL,
  CONSTRAINT PK_list_send_sms PRIMARY KEY(lssid),
  CONSTRAINT FK_list_send_sms_sms FOREIGN KEY (smsid) REFERENCES sms(smsid),
  CONSTRAINT FK_list_send_sms_parents FOREIGN KEY (pid) REFERENCES parents(pid)
)
GO
CREATE TABLE pages
(
  page_id int IDentity(1,1) NOT NULL,
  [uid] int NOT NULL,
  page_name nvarchar(40) NOT NULL,
  content text NOT NULL,
  position int NOT NULL,
  post_on datetime NOT NULL,
  CONSTRAINT PK_pages PRIMARY KEY(page_id),
  CONSTRAINT FK_pages_user FOREIGN KEY ([uid]) REFERENCES [user]([uid])
) 
GO
CREATE TABLE planning_board
(
  pbid int IDentity(1,1) NOT NULL,
  clid int NOT NULL,
  [uid] int NOT NULL,
  sjid int NOT NULL,
  half int NOT NULL,
  CONSTRAINT PK_planning_board PRIMARY KEY(pbid),
  CONSTRAINT FK_planning_board_class FOREIGN KEY (clid) REFERENCES class(clid),
  CONSTRAINT FK_planning_board_user FOREIGN KEY ([uid]) REFERENCES [user]([uid]),
  CONSTRAINT FK_planning_board_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
)
GO
CREATE TABLE register_sms
(
  rsid int IDentity(1,1) NOT NULL,
  pid int NOT NULL,
  scholastic nvarchar(150) NOT NULL,
  half int NOT NULL,
  CONSTRAINT PK_register_sms PRIMARY KEY(rsid),
  CONSTRAINT FK_register_sms_parents FOREIGN KEY (pid) REFERENCES parents(pid)
)
GO
CREATE TABLE teachers
(
  tid int IDentity(1,1) NOT NULL,
  [uid] int NOT NULL,
  name nvarchar(50) NOT NULL,
  birthday date,
  sex int,
  avatar nvarchar(50),
  email nvarchar(50),
  [address] nvarchar(250),
  phone nvarchar(15),
  bio text,
  sjid int,
  qualifications nvarchar(150),
  professional nvarchar(150),
  CONSTRAINT PK_teachers PRIMARY KEY(tid),
  CONSTRAINT FK_teachers_user FOREIGN KEY ([uid]) REFERENCES [user]([uid]),
  CONSTRAINT FK_teachers_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
)
GO
CREATE TABLE score_component_1
(
  scid int IDentity(1,1) NOT NULL,
  [sid] int NOT NULL,
  sjid int NOT NULL,
  scholastic nvarchar(150) NOT NULL,
  half int NOT NULL,
  number int NOT NULL,
  oral_test float,
  minute_15 float,
  minute_45 float,
  CONSTRAINT PK_score_component_1 PRIMARY KEY(scid),
  CONSTRAINT FK_score_component_1_students FOREIGN KEY ([sid]) REFERENCES students([sid]),
  CONSTRAINT FK_score_component_1_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
) 
GO
CREATE TABLE score_component_2
(
  scid int IDentity(1,1) NOT NULL,
  [sid] int NOT NULL,
  sjid int NOT NULL,
  scholastic nvarchar(150) NOT NULL,
  half int NOT NULL,
  number int NOT NULL,
  oral_test float,
  minute_15 float,
  minute_45 float,
  CONSTRAINT PK_score_component_2 PRIMARY KEY(scid),
  CONSTRAINT FK_score_component_2_students FOREIGN KEY ([sid]) REFERENCES students([sid]),
  CONSTRAINT FK_score_component_2_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
) 
GO
CREATE TABLE score_component_3
(
  scid int IDentity(1,1) NOT NULL,
  [sid] int NOT NULL,
  sjid int NOT NULL,
  scholastic nvarchar(150) NOT NULL,
  half int NOT NULL,
  number int NOT NULL,
  oral_test float,
  minute_15 float,
  minute_45 float,
  CONSTRAINT PK_score_component_3 PRIMARY KEY(scid),
  CONSTRAINT FK_score_component_3_students FOREIGN KEY ([sid]) REFERENCES students([sid]),
  CONSTRAINT FK_score_component_3_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
) 
GO
CREATE TABLE score_component_4
(
  scid int IDentity(1,1) NOT NULL,
  [sid] int NOT NULL,
  sjid int NOT NULL,
  scholastic nvarchar(150) NOT NULL,
  half int NOT NULL,
  number int NOT NULL,
  oral_test float,
  minute_15 float,
  minute_45 float,
  CONSTRAINT PK_score_component_4 PRIMARY KEY(scid),
  CONSTRAINT FK_score_component_4_students FOREIGN KEY ([sid]) REFERENCES students([sid]),
  CONSTRAINT FK_score_component_4_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
) 
GO
CREATE TABLE score_component_5
(
  scid int IDentity(1,1) NOT NULL,
  [sid] int NOT NULL,
  sjid int NOT NULL,
  scholastic nvarchar(150) NOT NULL,
  half int NOT NULL,
  number int NOT NULL,
  oral_test float,
  minute_15 float,
  minute_45 float,
  CONSTRAINT PK_score_component_5 PRIMARY KEY(scid),
  CONSTRAINT FK_score_component_5_students FOREIGN KEY ([sid]) REFERENCES students([sid]),
  CONSTRAINT FK_score_component_5_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
) 
GO
CREATE TABLE score_summary
(
  ssid int IDentity(1,1) NOT NULL,
  [sid] int NOT NULL,
  sjid int NOT NULL,
  scholastic nvarchar(25) NOT NULL,
  hk1 float,
  hk2 float,
  tbhk1 float,
  tbhk2 float,
  tbn float,
  CONSTRAINT PK_score_summary PRIMARY KEY(ssid),
  CONSTRAINT FK_score_summary_students FOREIGN KEY ([sid]) REFERENCES students([sid]),
  CONSTRAINT FK_score_summary_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid)
)
GO
CREATE TABLE teaching_schedule
(
 tcsid int IDentity(1,1) NOT NULL,
 [day] date NOT NULL,
 [time] nvarchar(30) NOT NULL,
 tid int NOT NULL,
 clid int NOT NULL,
 sjid int NOT NULL,
 CONSTRAINT PK_teaching_schedule PRIMARY KEY(tcsid),
 CONSTRAINT FK_teaching_schedule_subject FOREIGN KEY (sjid) REFERENCES [subject](sjid),
 CONSTRAINT FK_teaching_schedule_teachers FOREIGN KEY (tid) REFERENCES teachers(tid),
 CONSTRAINT FK_teaching_schedule_class FOREIGN KEY (clid) REFERENCES class(clid)
 )
 GO
 /*
 CREATE TABLE schedule
(
 [sid] int NOT NULL,
 mo nvarchar(50),
 tu nvarchar(50),
 we nvarchar(50),
 th nvarchar(50),
 fr nvarchar(50),
 sa nvarchar(50),
 su nvarchar(50),
 CONSTRAINT FK_schedule_students FOREIGN KEY ([sid]) REFERENCES students([sid])
 )
 GO */
 CREATE TABLE commend
 (
 cmid int IDentity(1,1) NOT NULL,
 [uid] int,
 contents nvarchar(150),
 datetime date,
 CONSTRAINT PK_commend PRIMARY KEY(cmid),
 CONSTRAINT FK_commend_user FOREIGN KEY ([uid]) REFERENCES [user]([uid])
 )
 GO
 CREATE TABLE list_commend
 (
  [uid] int,
  cmid int,
  CONSTRAINT FK_list_commend_user FOREIGN KEY ([uid]) REFERENCES [user]([uid]),
  CONSTRAINT FK_list_commend_commend FOREIGN KEY (cmid) REFERENCES commend(cmid)
 )
 GO